import pymysql

# 创建数据库连接
db = pymysql.connect(host='localhost', user='root', password='lcz19860109', db='dev10086', charset='utf8')

# 创建游标对象
cursor = db.cursor()

# 打开文件
with open('/mnt/origin/md5-own-one-20240123.txt', 'r') as f:
    batch_size = 10000  # 每次插入1000行
    values = []
    for line in f:
        # 每行数据格式如： 1a164cf210085e50dcbc7d0f79477db7        id_13
        # 使用空格分割每行数据
        md5, id = line.strip().split()
        # id转成bigint
        id = int(id.split('_')[1])
        values.append((md5, id))

        if len(values) >= batch_size:
            # 创建SQL语句
            sql = "INSERT INTO mobile_encrypt(phone, id) VALUES (%s, %s)"
            try:
                # 执行SQL语句
                cursor.executemany(sql, values)
                # 提交到数据库执行
                db.commit()
                values = []
            except:
                # 如果发生错误则回滚
                db.rollback()

    # 插入剩余的行
    if values:
        sql = "INSERT INTO mobile_encrypt(phone, id) VALUES (%s, %s)"
        try:
            cursor.executemany(sql, values)
            db.commit()
        except:
            db.rollback()

# 关闭数据库连接
db.close()